﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Light.Utility;

namespace Light.Framework
{
    /// <summary>
    /// 实体查询器(非泛型)
    /// </summary>
    public class EntityOperator
    {

        private static Column _primaryKey = null;
        /// <summary>
        /// 主键列
        /// </summary>
        protected static Column GetPrimaryKey(Type type)
        {
            if (_primaryKey == null)
                _primaryKey = ColumnMapper.GetPrimaryKey(type);
            return _primaryKey;
        }

        private static Dictionary<string, BelongsColumn> _belongsColumns = null;
        /// <summary>
        /// 外键列
        /// </summary>
        protected static Dictionary<string, BelongsColumn> GetBelongsColumns(Type type)
        {
            if (_belongsColumns == null)
                _belongsColumns = ColumnMapper.GetBelongsColumnsDic(type);
            return _belongsColumns;
        }

        /// <summary>
        /// 是否包含外键列
        /// </summary>
        private static bool HasBelongs(Type type)
        {
            Dictionary<string, BelongsColumn> columns = GetBelongsColumns(type);
            return columns != null && columns.Count > 0;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static OperateResult Create<T>(T obj)
        {
            OperateResult result = new OperateResult();

            //获取表名
            string tableName = ColumnMapper.GetTableName(typeof(T));
            List<Column> columns = ColumnMapper.GetColumns<T>();
            Dictionary<string, object> belongsKeyValues = ColumnMapper.GetBelongsKeyValue<T>(obj);

            StringBuilder builderSql = new StringBuilder();
            StringBuilder builderVal = new StringBuilder();

            //拼接SQL语句  INSERT INTO tableName(colum1,column2,......)
            builderSql.Append("INSERT INTO " + tableName);
            builderSql.Append("(");

            List<SqlParameter> listParam = new List<SqlParameter>();
            //一般类型列
            foreach (Column field in columns)
            {
                builderSql.Append(field.Name + ",");

                string paramName = "@" + field.Name;
                builderVal.Append(paramName + ",");

                SqlParameter param = new SqlParameter();
                param.ParameterName = paramName;
                param.SqlDbType = field.Type;
                param.Size = field.Size;
                param.Value = ReflectionHelper.GetPropertyValue<T>(obj, field.PropertyName);

                listParam.Add(param);
            }

            //外键关联列
            foreach (var columnName in belongsKeyValues.Keys)
            {
                builderSql.Append(columnName + ",");

                string paramName = "@" + columnName;
                builderVal.Append(paramName + ",");

                SqlParameter param = new SqlParameter();
                param.ParameterName = paramName;
                param.SqlDbType = SqlDbType.VarChar; //外键数据类型
                param.Value = belongsKeyValues[columnName];

                listParam.Add(param);
            }

            builderSql.Remove(builderSql.Length - 1, 1);
            builderSql.Append(")");

            //拼Values： VALUES(@p1,@p2,......);
            builderSql.Append("VALUES");
            builderSql.Append("(");
            builderSql.Append(builderVal.Remove(builderVal.Length - 1, 1).ToString());
            builderSql.Append(")");

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(builderSql.ToString(), listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "新增成功";
                }
            }
            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids">主键ID值</param>
        /// <returns></returns>
        public static OperateResult Delete<T>(params string[] ids)
        {
            OperateResult result = new OperateResult();

            if (ids == null || ids.Length == 0)
            {
                result.Message = "没有指定要删除的记录";
                return result;
            }

            string tableName = ColumnMapper.GetTableName<T>();
            Column field = ColumnMapper.GetPrimaryKey<T>();
            if (field == null)
                throw new Exception(string.Format("删除失败，类型 {0} 未映射主键 PrimaryKeyAttribute", typeof(T).Name));

            List<SqlParameter> listParam = new List<SqlParameter>();

            StringBuilder builder = new StringBuilder();
            int length = ids.Length;

            for (int i = 0; i < length; i++)
            {
                if (!String.IsNullOrEmpty(ids[i]))
                {
                    string paramName = "@" + field.Name + i.ToString();
                    builder.Append("DELETE FROM " + tableName + " WHERE " + field.Name + "=" + paramName + ";");
                    listParam.Add(new SqlParameter(paramName, ids[i]));
                }
            }
            using (ISQLExecuter excuter = SQLManager.CreateExecuter())
            {
                int count = excuter.ExecuteNonQuery(builder.ToString(), listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "删除成功";
                }
            }

            return result;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="obj">主键映射的字段必须有值</param>
        /// <returns></returns>
        public static OperateResult Delete<T>(T obj)
        {
            OperateResult result = new OperateResult();
            string tableName = ColumnMapper.GetTableName<T>();
            Column field = ColumnMapper.GetPrimaryKey<T>();
            if (field == null)
                throw new Exception(string.Format("删除失败，类型 {0} 未映射主键 PrimaryKeyAttribute", typeof(T).Name));

            object pValue = ReflectionHelper.GetPropertyValue(obj, field.PropertyName); ;
            if (pValue == null)
            {
                throw new Exception(string.Format("删除失败，关联主键的属性 {0} 值不能为空", field.PropertyName));
            }

            string paramName = "@" + field.Name;
            string sql = "DELETE FROM " + tableName + " WHERE " + field.Name + "=" + paramName;

            SqlParameter p = new SqlParameter();
            p.ParameterName = paramName;
            p.Value = ReflectionHelper.GetPropertyValue(obj, field.PropertyName);

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(sql, p);
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "删除成功";
                }
            }
            return result;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static OperateResult Update<T>(T obj)
        {
            OperateResult result = new OperateResult();

            //获取表名
            string tableName = ColumnMapper.GetTableName<T>();
            //获取主键
            Column primaryKey = ColumnMapper.GetPrimaryKey<T>();
            //获取系统类型列
            List<Column> columns = ColumnMapper.GetColumns<T>();
            //获取外键关联列 <键，值>
            Dictionary<string, object> belongsKeyValues = ColumnMapper.GetBelongsKeyValue<T>(obj);

            StringBuilder builderSql = new StringBuilder();

            //拼接SQL语句  UPDATE tableName SET colum1=@column1,column2=@column2,...... WHERE ID=@ID
            builderSql.Append("UPDATE " + tableName + " SET ");

            List<SqlParameter> listParam = new List<SqlParameter>();
            foreach (Column field in columns)
            {
                string paramName = "@" + field.Name;
                builderSql.Append(field.Name + "=" + paramName);
                builderSql.Append(",");

                SqlParameter param = new SqlParameter();
                param.ParameterName = paramName;
                param.SqlDbType = field.Type;
                param.Size = field.Size;
                param.Value = ReflectionHelper.GetPropertyValue<T>(obj, field.PropertyName);

                listParam.Add(param);
            }

            //外键关联列
            foreach (var columnName in belongsKeyValues.Keys)
            {
                string paramName = "@" + columnName;
                builderSql.Append(columnName + "=" + paramName);
                builderSql.Append(",");

                SqlParameter param = new SqlParameter();
                param.ParameterName = paramName;
                param.SqlDbType = SqlDbType.VarChar; //外键数据类型
                param.Value = belongsKeyValues[columnName];

                listParam.Add(param);
            }

            builderSql.Remove(builderSql.Length - 1, 1);

            //拼WHERE： VALUES(@p1,@p2,......);
            builderSql.Append(" WHERE " + primaryKey.Name + "=@" + primaryKey.Name);

            //执行SQL
            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                int count = executer.ExecuteNonQuery(builderSql.ToString(), listParam.ToArray());
                if (count > 0)
                {
                    result.IsSucced = true;
                    result.Message = "更新成功";
                }
            }

            return result;
        }

        /// <summary>
        /// 详情（支持关联查询）
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        public static T Get<T>(string id) where T : new()
        {
            T obj = default(T);

            #region 查询参数

            Column primaryKey = GetPrimaryKey(typeof(T));
            string paramName = "@" + primaryKey.Name;
            SqlParameter p = new SqlParameter();
            p.ParameterName = paramName;
            p.Value = id;

            #endregion

            #region 查询SQL

            string select = BuildSelectSql<T>(true);
            string from = BuildFromSql<T>();
            string where = " WHERE ";

            if (HasBelongs(typeof(T)))
                where += ColumnMapper.PRIMARY_TABLE_ALIAS + "." + primaryKey.Name + "=" + paramName;
            else
                where += primaryKey.Name + "=" + paramName;
            string sql = string.Format("SELECT {0} FROM {1} {2}", select, from, where);

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                DataSet ds = executer.ExecuteDataset(sql, p);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    obj = ConvertToEntity<T>(ds.Tables[0])[0];
                }
            }

            #endregion

            return obj;
        }

        /// <summary>
        /// 获取符合条件的一条记录
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <returns></returns>
        public static T Get<T>(SearchArgs searchArgs) where T : new()
        {
            T obj = default(T);
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string where = searchArgs.ToString<T>();
            List<SqlParameter> listParams = searchArgs.GetSqlParameters<T>();

            string select = BuildSelectSql<T>(true);
            string from = BuildFromSql<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string selectSql = string.Format(@"SELECT {0} FROM {1} {2}", select, from, where);
                DataSet ds = executer.ExecuteDataset(selectSql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                {
                    IList<T> objs = ConvertToEntity<T>(ds.Tables[0]);
                    if (objs != null && objs.Count > 0)
                        obj = objs[0];
                }
            }
            return obj;
        }

        /// <summary>
        /// 返回记录条数
        /// </summary>
        /// <param name="searchArgs">允许为null</param>
        /// <returns></returns>
        public static int Count<T>(SearchArgs searchArgs)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();

            string where = searchArgs.ToString<T>();
            string from = BuildFromSql<T>();
            List<SqlParameter> listParams = searchArgs.GetSqlParameters<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                string selectSql = string.Format(@"SELECT COUNT(*) FROM {0} WHERE {1}", from, where);
                object result = executer.ExecuteScalar(selectSql, listParams.ToArray());
                return StringHelper.ToInt(result);
            }
        }

        /// <summary>
        ///  获取符合条件的所有记录
        /// </summary>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="resultOrder">排序参数，可为null，为null时以主键排序</param>
        /// <returns></returns>
        public static List<T> GetAll<T>(SearchArgs searchArgs, ResultOrder resultOrder) where T : new()
        {
            DataTable dt = GetEntitiesTable<T>(searchArgs, resultOrder);
            return ConvertToEntity<T>(dt);
        }

        /// <summary>
        /// 分页查询，返回实体集合
        /// </summary>
        /// <param name="searchArgs">查询参数，可为null</param>
        /// <param name="pageArgs">分页参数，不能为null</param>
        /// <param name="resultOrder">排序参数，可为null，为null时以主键排序</param>
        /// <returns></returns>
        public static List<T> Search<T>(SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder) where T : new()
        {

            List<T> list = new List<T>();
            System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch();
            sw1.Start();

            DataTable dt = GetEntitiesTable<T>(searchArgs, pageArgs, resultOrder);

            sw1.Stop();

            if (dt != null && dt.Rows.Count > 0)
            {
                System.Diagnostics.Stopwatch sw2 = new System.Diagnostics.Stopwatch();
                sw2.Start();

                list = ConvertToEntity<T>(dt);

                sw2.Stop();
            }
            return list;
        }

        /// <summary>
        ///  将数据表转实体（关联表）
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertToEntity<T>(DataTable dt) where T : new()
        {
            List<T> list = new List<T>();
            int idx = 0;
            int count = dt.Rows.Count;

            List<Column> columns = ColumnMapper.GetColumns<T>();
            Dictionary<string, object> values = new Dictionary<string, object>();

            //逐行转换
            for (int i = 0; i < count; i++)
            {
                values.Clear();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn col in dt.Columns)
                {
                    //将列名替换成属性名
                    string cname = col.ColumnName.Replace(ColumnMapper.PREFIX_TABLE_ALIAS + idx + "_", "");
                    Column c = columns.Find(t => t.Name == cname);
                    if (c != null)
                        values.Add(c.PropertyName, dr[col]);
                }

                // 为 obj 基本类型赋值
                T obj = new T();
                ReflectionHelper.SetPropertyValue(obj, values);
                list.Add(obj);
            }

            Dictionary<string, BelongsColumn> belongsColumns = GetBelongsColumns(typeof(T));
            //遍历关联属性
            foreach (string propertyName in belongsColumns.Keys)
            {
                idx++;
                BelongsColumn bc = belongsColumns[propertyName];
                Type type = bc.Type;

                int cascade = propertyName.Split('.').Length;
                columns = ColumnMapper.GetColumns(type, cascade);

                //逐行转换
                for (int i = 0; i < count; i++)
                {
                    values.Clear();
                    DataRow dr = dt.Rows[i];
                    foreach (DataColumn col in dt.Columns)
                    {
                        string cname = col.ColumnName.Replace(bc.Alias + "_", "");
                        Column c = columns.Find(t => t.Name == cname);
                        if (c != null)
                            values.Add(propertyName + "." + c.PropertyName, dr[col]);
                    }
                    ReflectionHelper.SetPropertyValue(list[i], values);
                }
            }
            return list;
        }


        #region 私有方法

        /// <summary>
        /// 拼接结果列
        /// </summary>
        /// <param name="findOne">是否只取一条</param>
        /// <returns></returns>
        private static string BuildSelectSql<T>(bool findOne)
        {
            string sql = string.Empty;
            string top = findOne ? " TOP(1) " : string.Empty;

            if (HasBelongs(typeof(T)))
            {
                #region 有外键关联的情况

                //构建要查询的列 <表名，列s>
                string tableNameAlias = ColumnMapper.PRIMARY_TABLE_ALIAS;

                StringBuilder builderSelect = new StringBuilder();
                List<Column> tableColumns = ColumnMapper.GetColumns(typeof(T), 0);

                builderSelect.Append(top);

                //主表
                foreach (var c in tableColumns)
                {
                    string cname = tableNameAlias + "." + c.Name;
                    string calias = tableNameAlias + "_" + c.Name;
                    builderSelect.Append(cname + " AS " + calias);
                    builderSelect.Append(",");
                }

                Dictionary<string, BelongsColumn> belongsColumns = GetBelongsColumns(typeof(T));
                //关联表
                foreach (var propertyName in belongsColumns.Keys)
                {
                    //SELECT
                    BelongsColumn bc = belongsColumns[propertyName];

                    int cascade = propertyName.Split('.').Length;
                    List<Column> columns = ColumnMapper.GetColumns(bc.Type, cascade); //获取关联实体 数据库映射信息
                    foreach (var c in columns)
                    {
                        //  t1.ID AS t1_ID , t1.Name AS t1_Name
                        string cname = bc.Alias + "." + c.Name;
                        string calias = bc.Alias + "_" + c.Name;
                        builderSelect.Append(cname + " AS " + calias);
                        builderSelect.Append(",");
                    }
                }
                sql = builderSelect.Remove(builderSelect.Length - 1, 1).ToString();

                #endregion
            }
            else
            {
                sql = string.Format(" {0} * ", top);
            }
            return sql;
        }

        /// <summary>
        /// 拼接数据表
        /// </summary>
        /// <returns></returns>
        private static string BuildFromSql<T>()
        {
            string sql = string.Empty;
            string tableName = ColumnMapper.GetTableName<T>();

            if (HasBelongs(typeof(T)))
            {
                #region 有外键关联的情况

                string tableNameAlias = ColumnMapper.PRIMARY_TABLE_ALIAS;
                StringBuilder builderFrom = new StringBuilder();
                builderFrom.Append(tableName + " " + tableNameAlias);

                Dictionary<string, BelongsColumn> belongsColumns = GetBelongsColumns(typeof(T));
                //关联表
                foreach (var belongsPropertyName in belongsColumns.Keys)
                {
                    BelongsColumn bc = belongsColumns[belongsPropertyName];
                    string tname = ColumnMapper.GetTableName(bc.Type);

                    //LEFT JOIN
                    builderFrom.Append(" LEFT JOIN " + tname + " " + bc.Alias);
                    string belongsFK = bc.Name;
                    string belongsPK = ColumnMapper.GetPrimaryKey(bc.Type).Name;
                    string onAlias = !string.IsNullOrEmpty(bc.AliasReference) ? bc.AliasReference : tableNameAlias;

                    builderFrom.Append(" ON " + onAlias + "." + belongsFK + "=" + bc.Alias + "." + belongsPK);
                }
                sql = builderFrom.ToString();

                #endregion
            }
            else
            {
                sql = tableName;
            }
            return sql;
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <param name="resultOrder"></param>
        /// <returns></returns>
        private static DataTable GetEntitiesTable<T>(SearchArgs searchArgs, ResultOrder resultOrder)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();

            string select = BuildSelectSql<T>(false);
            string from = BuildFromSql<T>();
            string where = searchArgs.ToString<T>();
            string order = resultOrder.ToString<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                List<SqlParameter> listParams = searchArgs.GetSqlParameters<T>();
                string selectSql = string.Format(@"SELECT {0} FROM {1} {2} ORDER BY {3}", select, from, where, order);
                DataSet ds = executer.ExecuteDataset(selectSql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }

        /// <summary>
        /// 分页查询，返回转实体用的DataTable
        /// </summary>
        /// <param name="searchArgs"></param>
        /// <param name="pageArgs"></param>
        /// <param name="resultOrder"></param>
        /// <returns></returns>
        private static DataTable GetEntitiesTable<T>(SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder)
        {
            if (searchArgs == null)
                searchArgs = new SearchArgs();
            if (resultOrder == null)
                resultOrder = new ResultOrder();
            if (pageArgs == null)
                throw new Exception("分页参数 PageArgs 不能为空");

            string selectSql = BuildSelectSql<T>(false);
            string fromSql = BuildFromSql<T>();
            string whereSql = searchArgs.ToString<T>();
            string orderSql = resultOrder.ToString<T>();

            using (ISQLExecuter executer = SQLManager.CreateExecuter())
            {
                //获取总记录条数
                string countSql = string.Format("SELECT COUNT(*) FROM {0} {1}", fromSql, whereSql);
                List<SqlParameter> listParams = searchArgs.GetSqlParameters<T>();
                pageArgs.TotalResults = StringHelper.ToInt(executer.ExecuteScalar(countSql, listParams.ToArray()));


                //查询结果集
                string sql = string.Format(@"
                                SELECT {0}
                                FROM {1}
                                 {2}
                                ORDER BY {3}
                                OFFSET {4} ROWS
                                FETCH NEXT {5} ROWS ONLY", selectSql, fromSql, whereSql, orderSql, (pageArgs.PageIndex - 1) * pageArgs.PageSize, pageArgs.PageSize);


                DataSet ds = executer.ExecuteDataset(sql, listParams.ToArray());

                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
        }


        #endregion
    }
}
